Archive Magazine CD 1995/Archive Magazine CD 1995.iso/discs/prog_disc/volume_3/issue05/pipeline/edmundson/sheets/sheetnotes

Text File | 1989-12-17 | 10.7 KB | 202 lines

%OP%FC0,1
%CO:A,6,84%%H2%An example of a Spreadsheet.%H2%

From a spreadsheet point of view Pipedream 3 is much improved on version 2 with a
new recalculate technique and making full use of RiscOS and multiple windows.
Although the user guide on p. 180 describes a "preferred method" of interchanging
data between two or more sheets using dependent and supporting documents, the
example on this disc uses the technique of the linking file, to pass cash balances
from one monthly sheet to another.  If the five accounts on the sheet were to be
split into separate sheets in their own right, then I think the dependent document
technique would be the correct one to juggle figures from one to the other.

There is one function within a Pipedream sheet which I continue to see as a slight
weakness.  The spreadsheet is usually set to a global value for the number of
decimal places.  Since many sheets deal with money, this global value is two
decimal places.  If now one wishes to change a slot to another decimal place value,
say none, as when entering the day of the month, or the number of a cheque, which
are integers, this can be done if there is a number already entered into the slot
in question, but not if the slot is empty.  In other words, a slot will not hold
its special value for number of decimal places, if it is empty.  It will always
revert to the global value.  Therefore an empty slot has to register zero (0), when
it is not being used, if it is to retain its integer status and not take on the
global value of two decimal places.  Note that the global value is set from the
Options box in the Files menu, and the slot value is set from the Layout menu.

The spreadsheet example enclosed is a complex automatic sheet which takes account
of all payments, direct debits, etc. month by month and writes the carried forward
values to the next sheet each month.  The only manual entries at the keyboard are
for cheque payments, cash dispenser withdrawals and similar transactions.  Each
monthly sheet can be prepared from the Blank sheet when it is required.  I would
recommend that all the link files are prepared in advance and stored on disc.  This
will prevent confusing error messages appearing on the sheet if you try to use it
without the relevant link files existing.  They do clutter up the directory, but
since this section of the disc is purely for spreadsheet use, I don't think it
matters very much.

The sheet facilitates one Bank current account, one deposit account, and three
other accounts, labelled B.Soc l, 2, & 3.  meaning here Building Society accounts
but of course they could be any type of savings etc. account.

In general, to move from slot to slot horizontally to the right use Tab, and
Shift/Tab to reverse.  Use return to enter data.  Scroll across/down the sheet by
using the mouse pointer in the bottom/right hand scroll bar of the window.  Use
"First Column" and "Last Column" function keys to leap from side to side, and use
"Home" and "Copy" keys to jump from top to bottom.

 %H1%Investigating and Using the Sheet.%H1%

1. Select Mode 16.  There are three versions of the Blank sheet; Blank1 is the
plain sheet without borders;  Blank2 is the plain sheet with borders; Blank3 is the
sheet with borders and grid.

2. Load Blank2.  This sheet has the borders but no grid.  Note that rows 1-6 are
"fixed" - see the markers in the left border, so that the column headings remain
always in view.

3. Columns A-M are the manual "ledger" in which all credits and debits are entered
at the keyboard into each account.

4. Columns N-Q are the automatic section of the sheet, for entering all fixed
credits and debits throughout the year.

5. Columns R & S are the "work area" where many of the calculations are made.  This
simplifies the formulae in many other slots.  Column S is merely a shorthand
reminder of what the adjacent R slot is doing.  If the width of R & S were made = 0
they will still work but disappear from the screen.  I prefer to see them.

6. Column T rows 1-6, is a summary of all the balances of the accounts on the
sheet, which will be carried forward to next month's sheet via a link file.
(They are also reprinted in the heading of each account so that they can be seen
at a glance.)  Below these, rows 15-20, are the formulae which write these
balances to the link file.  Column V are the balances brought forward from the
previous month via another link file.  Column U is the shorthand label for slots on
either side.  "Chq" in row 12 is the number of the last cheque stub entered into
the current account (column B).

7. Position caret at B1.  The slot contains 0.  Type in the number of the month.
This is an important entry, used in many parts of the sheet.  It cannot be entered
automatically because if the sheet was examined a month or two later, the current
month would be entered and spoil the sheet.  As soon as the month-number is
entered, the balances from the previous month will be written into the sheet. That
is provided the link file exists.  I have prepared 6 dummy list files, for
experiment, numbered 12,1,2,3,4,5.  This means only January to April is permitted
i.e. months 1 to 4.  If you enter any other month number you will get errors.
Enter 1  January.  This will read in dummy data from the previous month (December).

8.  Move to H1.  Press "Delete to end of slot" to clear the zeros and copy in by
hand, the current date showing in E1.  This entry is not critical but it is
sometimes useful to know when you last used the sheet.

9. Try entering some data.  eg. In A7 enter the day of the month, B7 is the cheque
number which in this first slot has been entered automatically from the last sheet.
Type in the payee or whatever, in C7 then the sum spent in D7.  Be sure to rename a
sheet before saving, or you will overwrite the examples and lose them.  A word
about slot R1.  In theory this is a copy of the month number in B1.  But the
formula in R1 says:
                                    IF(B1<1|B1>12,1,B1)

If B1 is less than 1 or greater than 12, R1=1, otherwise R1=B1.  To a limited
extent this traps mistakes in month numbers.  Its main purpose is to prevent error
messages appearing in some columns of the blank sheet where most slots are
deliberately set to zero, to keep the blank sheet neutral until valid data is
entered.

10. Now load in a partially completed example sheet by loading Sheet 1.  Consider
columns N,O,P,Q.  Rows 7-22 are concerned with fixed debits from the current
account.  They are labelled in column O.  The day of the month on which they are
debited is in col. N.  The sum to be debited is in col. P. The actual debit is
governed by the formula in slot Q.  Therefore along row 7, we have the second of
the month, the water instalment of £16.66, and the formula in Q7 which says:

                                   IF($R$20>=N7,P7,0)

When the sheet was set up, this column was replicated down the column.  The slot
R20 had to be fixed throughout and to hold it fixed, the $ signs had to be
included.  R20 is the latest day of the month entered into column A.  If this day
equals the number in N7 (2) or is beyond this day, then the value in slot P7 has to
be entered into Q7, or else 0 has to be entered.  When 16.66 is entered into Q7 it
is deducted from the account, but remains zero otherwise.  Thus on the second day
of every month, the water instalment is deducted from the current account balance.
The same argument applies down the columns with the other fixed debits.

11.  The three entries in rows 19-21 are slightly different.  These are annual
debits, not monthly ones.  Consider row 21 the television licence debit.  The
formula in Q21 says:
"If the day of the month is equal or beyond the number in N21 (2) and if the month
of the year (R1) is December (12) then deduct £66 otherwise zero."

                              IF(R20>=N21&R1=12,P21,0)

12.  Lower down these columns are the fixed credits for various accounts.  These
operate in much the same way.  Some have more complex conditions. Consider row 27.
Here the current account is to be credited with a dividend on the 15th of June and
December.  The sum which will be received is in P27.  The slot says "If the day of
the month is 15 or beyond and the month is June or the month is December enter into
slot Q27 the value in slot P27 otherwise don't enter anything."  This is coded as:

                             If(R20>=N27&(R1=6|R1=12),P27,0)

Slot Q26 refers to Interest which is paid monthly but varies between £25 or £50
according to the month.  Here the desired conditional statement is: "If the maximum
value in column A (the latest day) is equal to the 11th of the month or beyond,
then if it is January, May, July or November enter £25 into slot Q26, otherwise
enter £50."  I felt this was too much to write in one slot and so I used another
slot in the work area, R22, to do part of the calculation.  Slot R22 calculates
whether the sum to be entered is £25 or £50 as follows: "If the number of the month
is 1 or 5 or 7 or 11 then enter £25 otherwise enter £50."

                             IF(R1=1|R1=5|R1=7|R1=11,25,50)

This allows Q26 to be simplified to:

                             IF(R20>=N26,R22,0)

By studying the sheet all the other formulae can be analysed in the same way.

%H1%Preparing Link Files.%H1%

This is clearly described in pp 281-3.  In this sheet example, the link file must
have 1 column and 6 rows to receive the data carried forward from slots T7 - T12.
The instructions to write the data are in slots T16 - T20.  eg T16 says:

                             WRITE(R1,1,1,T7)

and so on.  Slots V7 - V12 read the data from the previous months balances.  The
formulae are related to slot V1.  Slot V1 compensates for the jump between December
and January, ie. between month 12 and 1.  If the current month is 1, data must be
read from link file 12.  In all other cases it is read from the current month
minus 1.  Hence slot V1 says:

                            IF(R1=1,12,R1-1)

Thus the value in V1 is always one less than the current month number but for
January, it equals 12.  The "read" slots V7-V12 then read from the link file whose
number is designated by V1.  There is a further slight complication because the
sheet returns zero in all these slots until it is activated.  (This prevents
unsightly error messages).  Thus the formula in these slots is:

                            IF(B1=0,0,READ(V1,1,1))

and so on.  Immediately the month number is entered in B1, the slots register the
balances brought forward from the previous month and the sheet is activated.
%CO:B,12,72%%CO:C,12,66%%CO:D,12,54%%CO:E,12,42%%CO:F,12,30%


















%CO:G,12,18%%CO:H,6,6%